﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.Configuration;

namespace WCFService
{
    public class SQLiteDA
    {
        //String dbConnection = @"Data Source=D:\Java\Android\AndroidDemo\qtandroiddemo\Source\WCFService\WCFService\App_Data\AndroidDemo.db3";
        string dbConnection = ConfigurationManager.ConnectionStrings["SQLiteConnString"].ConnectionString;

        public DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();
            try
            {
                SQLiteConnection cnn = new SQLiteConnection(dbConnection);
                SQLiteCommand mycommand = new SQLiteCommand(cnn);
                mycommand.CommandText = sql;
                SQLiteDataAdapter da = new SQLiteDataAdapter(mycommand);
                da.Fill(dt);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            return dt;
        }

        public DataSet GetDataSet(string sql)
        {
            DataSet ds = new DataSet();
            try
            {
                SQLiteConnection cnn = new SQLiteConnection(dbConnection);
                SQLiteCommand mycommand = new SQLiteCommand(cnn);
                mycommand.CommandText = sql;
                SQLiteDataAdapter da = new SQLiteDataAdapter(mycommand);
                da.Fill(ds);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            return ds;
        }

        public int ExecuteNonQuery(string sql)
        {
            int successState = 0;
            SQLiteConnection cnn = new SQLiteConnection(dbConnection);
            cnn.Open();
            using (SQLiteTransaction mytrans = cnn.BeginTransaction())
            {
                SQLiteCommand mycommand = new SQLiteCommand(sql, cnn);
                try
                {
                    mycommand.CommandTimeout = 180;
                    successState = mycommand.ExecuteNonQuery();
                    mytrans.Commit();
                }
                catch (Exception e)
                {
                    mytrans.Rollback();
                }
                finally
                {
                    mycommand.Dispose();
                    cnn.Close();
                }
            }
            return successState;
        }

        public int ExecuteNonQuery(string sql, IList<SQLiteParameter> cmdparams)
        {
            int successState = 0;
            SQLiteConnection cnn = new SQLiteConnection(dbConnection);
            cnn.Open();
            using (SQLiteTransaction mytrans = cnn.BeginTransaction())
            {
                SQLiteCommand mycommand = new SQLiteCommand(sql, cnn, mytrans);
                try
                {
                    mycommand.Parameters.AddRange(cmdparams.ToArray());
                    mycommand.CommandTimeout = 180;
                    successState = mycommand.ExecuteNonQuery();
                    mytrans.Commit();
                }
                catch (Exception e)
                {
                    mytrans.Rollback();
                    throw e;
                }
                finally
                {
                    mycommand.Dispose();
                    cnn.Close();
                }
            }
            return successState;
        }

        public string ExecuteScalar(string sql)
        {
            SQLiteConnection cnn = new SQLiteConnection(dbConnection);
            try
            {
                cnn.Open();
                SQLiteCommand mycommand = new SQLiteCommand(cnn);
                mycommand.CommandText = sql;
                object value = mycommand.ExecuteScalar();
                if (value != null)
                {
                    return value.ToString();
                }
            }
            finally
            {
                cnn.Close();
            }
            return "";
        }
    }
}
